package cn.andy.android.crm.db;

import java.util.ArrayList;
import java.util.List;

import cn.andy.android.crm.beans.PersonInfo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;


public class PersonDBHelper extends SQLiteOpenHelper{
	
	private static final String TAG = "SQLiteDBHelper";
	
	private static final int DATABASE_VERSION = 3;
	

	public PersonDBHelper(Context context) {
		super(context, PersonDB.DBNAME, null, DATABASE_VERSION);
		context.openOrCreateDatabase(PersonDB.DBNAME, Context.MODE_PRIVATE, null);
		createTableSales();
	}
	
	public void createTableSales() {
		SQLiteDatabase db = getWritableDatabase();
        try {  
			db.execSQL("CREATE TABLE " 
					+ PersonTable.TABLENAME + " ( "
					+ PersonTable.ID + " INTEGER PRIMARY KEY autoincrement, "
					+ PersonTable.NAME + " TEXT, " 
					+ PersonTable.EMAIL + " TEXT, "
					+ PersonTable.ADDRESS + " TEXT ); ");
			Log.v(TAG, " Create Table " + PersonTable.TABLENAME + " ok ");  
        } catch (Exception e) {  
            Log.v(TAG, " Create Table " + PersonTable.TABLENAME + " errer , table already exists.");  
        }  
    }  

	@Override
	public void onCreate(SQLiteDatabase db) {
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		
	}
	
	public long insert(PersonInfo sData){
		SQLiteDatabase db = getWritableDatabase();
		
		ContentValues values = new ContentValues();
		values.put(PersonTable.NAME, sData.getName());
		values.put(PersonTable.EMAIL, sData.getEmail());
		values.put(PersonTable.ADDRESS, sData.getAddress());
		
		try {
			return db.insert(PersonTable.TABLENAME, null, values);
		} finally {
			db.close();
		}
	}
	
	public int update(PersonInfo sData, int id){
		SQLiteDatabase db = getWritableDatabase();
		
		ContentValues values = new ContentValues();
		values.put(PersonTable.NAME, sData.getName());
		values.put(PersonTable.EMAIL, sData.getEmail());
		values.put(PersonTable.ADDRESS, sData.getAddress());
		
		try {
			return db.update(PersonTable.TABLENAME, values, 
					PersonTable.ID + " = ? ", new String[]{String.valueOf(id)});
		} finally {
			db.close();
		}
	}
	
	public int delete(int id){
		SQLiteDatabase db = getWritableDatabase();
		try {
			return db.delete(PersonTable.TABLENAME, 
					PersonTable.ID + " = ? ", 
					new String[]{String.valueOf(id)});
		} finally {
			db.close();
		}
	}
	
	public Cursor select(String where, String[] param){
		SQLiteDatabase db = getReadableDatabase();
		return db.query(PersonTable.TABLENAME, new String[]{
				PersonTable.ID, PersonTable.NAME, PersonTable.EMAIL,
				PersonTable.ADDRESS}, 
				where, param, null, null, null);
	}
	
	public Cursor select(){
		return select(null, null);
	}
	
	public Cursor selectBySQL(String sql, String[] params){
		SQLiteDatabase db = getReadableDatabase();
		return db.rawQuery(sql, params);
	}
	
	public Cursor selectBySQL(String sql){
		return selectBySQL(sql, null);
	}
	
	public List<PersonInfo> get(Cursor cursor){
		List<PersonInfo> persons = new ArrayList<PersonInfo>();
		
		int num = cursor.getCount();
		if(num > 0){
			cursor.moveToFirst();
			for (int i = 0; i < num; i++) {
				PersonInfo person = new PersonInfo();
				person.setId(cursor.getInt(cursor.getColumnIndex(PersonTable.ID)));
				person.setName(cursor.getString(cursor.getColumnIndex(PersonTable.NAME)));
				person.setEmail(cursor.getString(cursor.getColumnIndex(PersonTable.EMAIL)));
				person.setAddress(cursor.getString(cursor.getColumnIndex(PersonTable.ADDRESS)));
				
				persons.add(person);
				cursor.moveToNext();
			}
		}
		
		cursor.close();
		
		return persons;
	}

}
